package com.epam.dao.h2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.epam.dao.TrainingDao;
import com.epam.database.ConnectionPool;
import com.epam.entity.Training;

public class H2TrainingDao extends H2AbstractEntityDao <Training>implements
		TrainingDao<Training> {
	private static Logger LOGGER = LoggerFactory.getLogger(H2TrainingDao.class);
	
	private final static String SELECT_FOR_STUDENT = "SELECT tr.training_id, c.course_id, "
			+ "c.course_NAME, c.training_period, c.hours, tr.TRAINING_MARK, "
			+ "tr.trAINING_REVIEW, tr.student_id, p.person_name"
			+ " FROM training AS tr INNER JOIN courses AS c"
			+ " ON tr.course_id=c.course_id"
			+ " inner join teachers as t"
			+ " on c.teacher_id = t.teacher_id"
			+ " inner join persons as p" 
			+ " on t.person_id = p.person_id"
			+ " WHERE tr.student_id=?"
			+ " ORDER BY tr.training_id";
	private final static String SELECT_FOR_TEACHER = "SELECT tr.training_id,c.course_id, "
			+ "c.course_NAME, p.person_name, tr.TRAINING_MARK, tr.trAINING_REVIEW"
			+ " FROM training AS tr"
			+ " INNER JOIN courses AS c ON tr.course_id=c.course_id"
			+ " INNER JOIN  teachers AS t ON c.teacher_id = t.teacher_id"
			+ " INNER JOIN students AS st  ON st.student_id =tr.student_id"
			+ " INNER JOIN persons AS p ON st.person_id=p.person_id"
			+ " WHERE t.teacher_id=? "
			+ " ORDER BY tr.training_id";
	private final static String UPDATE_MARK_RAVIEW ="UPDATE training" 
			+ " SET training_mark  = ?, "
			+ "training_review = ?" 
			+ " WHERE training_id = ?";
	private final static String ADD_TRAINING = "INSERT INTO training (course_id, "
			+ "student_id, training_start, training_end) "
			+ " VALUES (?, ?, '2013-11-10', '2013-12-12')";
	private final static String SELECT_BY_ID = "SELECT tr.training_id, c.course_id, "
			+ "c.course_NAME, c.training_period, c.hours, tr.TRAINING_MARK, "
			+ "tr.trAINING_REVIEW  , tr.student_id, p.person_name"
			+ " FROM training as tr inner join courses as c "
			+ "	on tr.course_id=c.course_id "
			+ "	inner join teachers as t on c.teacher_id = t.teacher_id"
			+ "	inner join persons as p on t.person_id = p.person_id "
			+ "	where tr.training_id= (SELECT Max(training_id) FROM training) and tr.student_id = ?";	
	
	

	@Override
	public Training selectById(long id) {
		Training training = null;
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = ConnectionPool.getInstance().takeConnection();
			pst = conn.prepareStatement(SELECT_BY_ID);
			pst.setLong(1, id);
			rs = pst.executeQuery();
			if (rs.next()) {
				training = new Training(rs.getLong("training_id"),
						rs.getLong("course_id"), rs.getString("course_name"),
						rs.getString("person_name"),
						rs.getString("training_period"), rs.getInt("hours"),
						rs.getInt("training_mark"),
						rs.getString("training_review"));
			}
		} catch (Exception e) {
			LOGGER.warn("Failed to create the object");
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (conn != null) {

					ConnectionPool.getInstance().returnConnection(conn);
				}
			} catch (SQLException e) {
				LOGGER.error("Error  closing request SelectById");
			}
		}
		return training;
	}
	
	@Override
	public List<Training> selectForStudent(long id) {
		List<Training> trainingList = new ArrayList<Training>();
		Training training = null;
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = ConnectionPool.getInstance().takeConnection();
			pst = conn.prepareStatement(SELECT_FOR_STUDENT);
			pst.setLong(1, id);
			rs = pst.executeQuery();
			while (rs.next()) {
				training = new Training(rs.getLong("training_id"),
						rs.getLong("course_id"), rs.getString("course_name"),
						rs.getString("person_name"),
						rs.getString("training_period"), rs.getInt("hours"),
						rs.getInt("training_mark"),
						rs.getString("training_review"));
				trainingList.add(training);
			}
		} catch (Exception e) {
			LOGGER.warn("Failed to create the object");
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (conn != null) {

					ConnectionPool.getInstance().returnConnection(conn);
				}
			} catch (SQLException e) {
				LOGGER.error("Error  closing request SelectTrainingForStudent");
			}
		}
		return trainingList;
	}
	
	@Override
	public List<Training> selectForTeacher(long id) {
		List<Training> trainingList = new ArrayList<Training>();
		Training training = null;
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = ConnectionPool.getInstance().takeConnection();
			pst = conn.prepareStatement(SELECT_FOR_TEACHER);
			pst.setLong(1, id);
			rs = pst.executeQuery();
			while (rs.next()) {
				training = new Training(rs.getLong("training_id"),
						rs.getLong("course_id"), rs.getString("course_name"),
						rs.getString("person_name"),
						rs.getInt("training_mark"),
						rs.getString("training_review"));
				trainingList.add(training);
			}
		} catch (Exception e) {
			LOGGER.warn("Failed to create the object");
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (conn != null) {

					ConnectionPool.getInstance().returnConnection(conn);
				}
			} catch (SQLException e) {
				LOGGER.error("Error  closing request SelectTrainingForTeacher");
			}
		}
		return trainingList;
	}
	
	@Override
	public void saveOrUpdate(Training entity) {
		int updateResult;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = ConnectionPool.getInstance().takeConnection();
			pst = conn.prepareStatement(UPDATE_MARK_RAVIEW);
			pst.setInt(1, entity.getTrainingMark());
			pst.setString(2, entity.getTrainingReview());
			pst.setLong(3, entity.getId());
			updateResult = pst.executeUpdate();
			LOGGER.info("Updated " + updateResult + "rows");
		} catch (Exception e) {
			LOGGER.warn("Failed to update the object");
		} finally {
			try {
				if (pst != null)
					pst.close();
				if (conn != null) {
					conn.commit();
					ConnectionPool.getInstance().returnConnection(conn);
				}
			} catch (SQLException e) {
				LOGGER.error("Error  closing request UpdateTraining");
			}
		}
	}
	
	@Override
	public int insert(long courseId, long studentId) {
		int isertResult = 0;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = ConnectionPool.getInstance().takeConnection();
			pst = conn.prepareStatement(ADD_TRAINING);
			pst.setLong(1, courseId);
			pst.setLong(2, studentId);
			isertResult = pst.executeUpdate();
			LOGGER.info("Insert into Training" + isertResult + "rows");			
		} catch (Exception e) {
			LOGGER.warn("Failed to update the object");
		} finally {
			try {
				if (pst != null)
					pst.close();
				if (conn != null) {
					conn.commit();
					ConnectionPool.getInstance().returnConnection(conn);
				}
			} catch (SQLException e) {
				LOGGER.error("Error  closing request InsertTraining");
			}
		}
		return isertResult;
	}

	@Override
	public void update(long trainingId, int mark, String review) {
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = ConnectionPool.getInstance().takeConnection();
			pst = conn.prepareStatement(UPDATE_MARK_RAVIEW);
			pst.setInt(1, mark);
			pst.setString(2, review);
			pst.setLong(3, trainingId);
			pst.executeUpdate();			
		} catch (Exception e) {
			LOGGER.warn("Failed to update the mark");
		} finally {
			try {
				if (pst != null)
					pst.close();
				if (conn != null) {
					conn.commit();
					ConnectionPool.getInstance().returnConnection(conn);
				}
			} catch (SQLException e) {
				LOGGER.error("Error  closing request UpdateTraining");
			}
		}
	}
	

}
